package my.jdbc;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import java.util.regex.Matcher;

import my.sql.ConnectionPool;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import transaction.TransactionCallBack;

public class JdbcUtil {
	private static final Log log = LogFactory.getLog(JdbcUtil.class);

	public static List<Object> formatBeanToFields(Object bean, Matcher matcher) {
		List<Object> fields = new ArrayList<Object>();
		try {
			while (matcher.find()) {
				Field field = bean.getClass().getDeclaredField(matcher.group(1));
				field.setAccessible(true);
				fields.add(field.get(bean));
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return fields;
	}

	@SuppressWarnings("unused")
	private static int getFieldCount(Matcher matcher) {
		int fieldCount = 0;
		while (matcher.find()) {
			fieldCount++;
		}
		matcher.reset();
		return fieldCount;
	}

	public static <T> List<T> buildSqlForListBean(Connection con, String sql, Class<T> c, List<Object> fields, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(sql);
			if (fields != null) {
				for (int i = 0; i < fields.size(); i++) {
					statement.setObject(i + 1, fields.get(i));
				}
			}
			result = statement.executeQuery();
			ResultSetMetaData tableData = result.getMetaData();
			int cloumnCounts = tableData.getColumnCount();
			List<T> objs = new ArrayList<T>();
			while (result.next()) {
				T obj = c.newInstance();
				for (int i = 1; i <= cloumnCounts; i++) {
					if (result.getObject(i) != null) {
						Field field = null;
						try {
							field = c.getDeclaredField(StringUtils.uncapitalize(tableData.getColumnLabel(i)));
							field.setAccessible(true);
							field.set(obj, result.getObject(i));
						} catch (Exception e) {
							log.info(c.getName() + "--->" + e);
						}
					}
				}
				objs.add(obj);
			}
			return objs;
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
			e.printStackTrace();
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return null;
	}

	public static List<Object[]> buildSqlForListField(Connection con, String sql, List<Object> fields, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(sql);
			if (fields != null) {
				for (int i = 0; i < fields.size(); i++) {
					statement.setObject(i + 1, fields.get(i));
				}
			}
			result = statement.executeQuery();
			ResultSetMetaData tableData = result.getMetaData();
			int cloumnCounts = tableData.getColumnCount();
			List<Object[]> objs = new ArrayList<Object[]>();
			while (result.next()) {
				Object[] obj = new Object[cloumnCounts];
				for (int i = 1; i <= cloumnCounts; i++) {
					obj[i - 1] = result.getObject(i);
				}
				objs.add(obj);
			}
			return objs;
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
			e.printStackTrace();
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return null;
	}

	public static Vector<Object> buildSqlForVectorField(Connection con, String sql, List<Object> fields, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(sql);
			if (fields != null) {
				for (int i = 0; i < fields.size(); i++) {
					statement.setObject(i + 1, fields.get(i));
				}
			}
			result = statement.executeQuery();
			ResultSetMetaData tableData = result.getMetaData();
			int cloumnCounts = tableData.getColumnCount();
			Vector<Object> objs = new Vector<Object>();
			while (result.next()) {
				Vector<Object> rowData = new Vector<Object>();
				for (int i = 1; i <= cloumnCounts; i++) {
					rowData.add(result.getObject(i));
				}
				objs.add(rowData);
			}
			return objs;
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
			e.printStackTrace();
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return null;
	}

	public static Vector<String> getTableColumnNameBySql(Connection con, String sql, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(sql);
			result = statement.executeQuery();
			ResultSetMetaData tableData = result.getMetaData();
			int cloumnCounts = tableData.getColumnCount();
			Vector<String> objs = new Vector<String>();
			while (result.next()) {
				for (int i = 1; i <= cloumnCounts; i++) {
					objs.add(tableData.getColumnName(i));
				}
			}
			return objs;
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
			e.printStackTrace();
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return null;
	}

	public static boolean executeUpdate(Connection con, String sql, List<Object> fields, String typeName) {
		PreparedStatement statement = null;
		try {
			statement = con.prepareStatement(sql);
			if (fields != null) {
				for (int i = 0; i < fields.size(); i++) {
					Object field = fields.get(i);
					if (field instanceof Date) {
						field = DateFormatUtils.format((Date) field, "yyyy-MM-dd HH:mm:ss");
					}
					statement.setObject(i + 1, field);
				}
			}
			return statement.executeUpdate() > 0;
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
			e.printStackTrace();
			return false;
		} finally {
			ConnectionPool.closeConnection(con, typeName, null, statement);
		}
	}

	/**
	 * JDBC 事务
	 */
	public static void beginJdbcTransaction(Connection con, TransactionCallBack tc, String typeName) {
		Statement statement = null;
		try {
			con.setAutoCommit(false);
			statement = tc.execute(con);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			System.out.println("already rollback .....");
		} finally {
			try {
				if (statement != null) {
					statement.close();
					System.out.println("already close statement .....");
				}
				con.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			ConnectionPool.closeConnection(con, typeName, null, null);
		}
	}
	
	

	// 检测连接是否有效
	public static boolean isConOk(Connection con, String typeName) {
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = con.prepareStatement(" select 1 ");
			result = statement.executeQuery();
			while (result.next()) {
				return true;
			}
		} catch (Exception e) {
			ConnectionPool.clearAllConnection(typeName);
		} finally {
			ConnectionPool.closeConnection(con, typeName, result, statement);
		}
		return false;
	}

}
